package utils;

import java.io.*;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;



public class DBUtil {
	private static String url=null;
	private static String userName=null;
	private static String pwd=null;
	static
	{
		try {
			InputStream in=DBUtil.class.getClassLoader().getResourceAsStream("config/jdbc.properties");
			//FileInputStream in=new FileInputStream(new File("jdbc.properties"));
			Properties p=new Properties();
			p.load(in);
			url=p.getProperty("url");
			userName=p.getProperty("username");
			pwd=p.getProperty("password");
			Class.forName(p.getProperty("drivername"));
		} catch (ClassNotFoundException | IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static Connection getConnection()
	{
		Connection conn=null;
		try {
			conn=DriverManager.getConnection(url,userName,pwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public static void createDataBase(String sql)
	{
		Connection conn=getConnection();
		if(conn!=null)
		{
			java.sql.Statement st;
			try {
				st = conn.createStatement();
				st.execute(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public static void createDataTable(String databaseName,String sql)
	{
		Connection conn=getConnection();
		String sql1="use "+databaseName;
		if(conn!=null)
		{
			java.sql.Statement st;
			try {
				st = conn.createStatement();
				st.execute(sql1);
				st.execute(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public static void insertData()
	{
		Connection conn=getConnection();
		String sql="insert into user(username,password) values(?,?)";
		if(conn!=null)
		{
			try {
				PreparedStatement ps=conn.prepareStatement(sql);
				ps.setString(1,"jack");
				ps.setString(2, "1234");
				ps.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
	}

	//对数据表的增、删、改
	//String sql="delete  from user where id=? and name=? and pwd=?";
	public static int updateData(String sql,Object...objects)
	{
		Connection conn=getConnection();
		if(conn!=null)
		{
			try {
				PreparedStatement ps=conn.prepareStatement(sql);
				for(int i=1;i<=objects.length;i++)
				{
					ps.setObject(i,objects[i-1]);
				}
				return ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return 0;
	}
	public static <T> List<T> query (Class<T> clazz, String sql, Object... args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<T> list = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);//可变形参可看作数组
			}
			rs = ps.executeQuery();
			list = new ArrayList<>();
			while (rs.next()) {//next()作用：判断结果集的下一条是否有数据，则返回true并下移指针，否则返回false指针不动
				ResultSetMetaData rsmd = ps.getMetaData();//获取结果集的元数据
				int columnCount = rsmd.getColumnCount();//获取列数
				T t = clazz.newInstance();//通过反射常见对象
				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					String columnLabel = rsmd.getColumnLabel(i + 1);//获取列的别名
					//通过反射给columnLable列赋值为columnValue
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columnValue);
				}
				list.add(t);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		}
		return null;
	}
}
